using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Collections;
using System.Web.UI.HtmlControls;
using System.Globalization;
using System.Web.Script.Services;
using System.Web.Services;

/// <summary>
/// A page for managing translations for applications
/// </summary>
public partial class Translations : System.Web.UI.Page
{
    private Database db = new Database();
    private static int editButtonColumNo = 3;
    private SortedList<int, string> translationIds = new SortedList<int, string>();

    /// <summary>
    /// Displays available applications and handles JavaScript for the full-window tranlation editor
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack) {
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT id, displayname FROM applications ORDER BY name ASC";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                radApplication.DataSource = ds;
                radApplication.DataValueField = "id";
                radApplication.DataTextField = "displayname";
                radApplication.DataBind();
                if(radApplication.Items.Count > 0) {
                    radApplication.SelectedIndex = 0;
                    ShowLanguages(null, EventArgs.Empty);
                    mltMain.ActiveViewIndex = 1;
                }
            }
            Session["crossTabColumns"] = string.Empty;
        } else {
            Page.ClientScript.RegisterClientScriptInclude("main", ResolveUrl("~/js/main.js"));
            Page.ClientScript.RegisterClientScriptInclude("jquery", "http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js");//ResolveUrl("~/js/jquery-1.2.6.min.js"));
            Page.ClientScript.RegisterClientScriptInclude("json", ResolveUrl("~/js/json.js"));
            Page.ClientScript.RegisterClientScriptInclude("translationjs", ResolveUrl("~/js/translations.js"));
            Master.RenderJSArrayWithCliendIds(fullsizeholder, cboAddTranslationId);
            if(mltMain.ActiveViewIndex == 2) {
                PopulateAddFormLanguages();
            }
        }
    }

    /// <summary>
    /// Closes the database connection when rendering is done.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Unload(object sender, EventArgs e) {
        if(db != null && db.Connected) {
            db.CloseConnection();
        }
    }
    
    /// <summary>
    /// Shows the languages and bundles for the application selected.
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void ShowLanguages(object sender, EventArgs e) {
        if(radApplication.SelectedValue == null) {
            return;
        }
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT languages FROM applications WHERE id = @p";
            cmd.AddParameterWithValue("@p", radApplication.SelectedValue);
            ListComparer cmp = new ListComparer();
            List<ListItem> tempLanguages = new List<ListItem>(),
                           tempBundles = new List<ListItem>();
            lstLanguages.Items.Clear();
            string rawLanguages = cmd.ExecuteScalar().ToString();
            foreach(string abbrev in rawLanguages.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) {
                ListItem itm = null;
                try {
                    itm = new ListItem(CultureInfo.GetCultureInfo(abbrev).EnglishName, abbrev);
                } catch {
                    itm = new ListItem(abbrev);
                }
                itm.Selected = true;
                tempLanguages.Add(itm);
            }
            tempLanguages.Sort(cmp);
            lstLanguages.Items.AddRange(tempLanguages.ToArray());
            if(lstLanguages.Items.Count == 0) {
                ListItem itm = new ListItem("No languages found", "");
                itm.Selected = true;
                lstLanguages.Items.Add(itm);
                lstLanguages.Enabled = false;
            } else {
                lstLanguages.Enabled = true;
            }

            lstBundles.Items.Clear();
            cmd.CommandText = "SELECT id, name FROM bundles WHERE application = @p";
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) {
                ListItem itm = new ListItem(reader["name"].ToString(), reader["id"].ToString());
                itm.Selected = true;
                tempBundles.Add(itm);
            }
            tempBundles.Sort(cmp);
            lstBundles.Items.AddRange(tempBundles.ToArray());
            if(lstBundles.Items.Count == 0) {
                ListItem itm = new ListItem("No bundles found", "");
                itm.Selected = true;
                lstBundles.Items.Add(itm);
                lstBundles.Enabled = false;
            } else {
                lstBundles.Enabled = true;
            }
            reader.Close();
        }
    }
    
    /// <summary>
    /// Adds textboxes for each selected language to the 'New Element' form
    /// </summary>
    private void PopulateAddFormLanguages() {
        tblAddTranslation.Rows.Clear();
        StringBuilder jqueryBuffer = new StringBuilder();
        string defaultLanguage = Session["defaultLanguage"].ToString();
        Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
        KeyValuePair<string, string>[] addColIndex;
        if(columnIndex == null) {
            columnIndex = new Dictionary<string, int>();
            List<string> tmpLanguages = new List<string>();
            foreach(ListItem itm in lstLanguages.Items) {
                if(itm.Selected) {
                    tmpLanguages.Add(itm.Value);
                }
            }
            tmpLanguages.Sort();
            tmpLanguages.Remove(defaultLanguage);
            tmpLanguages.Insert(0, defaultLanguage);
            string[] languages = tmpLanguages.ToArray();
            addColIndex = new KeyValuePair<string, string>[tmpLanguages.Count];
            for(int i = 0; i < languages.Length; i++) {
                addColIndex[i] = new KeyValuePair<string, string>(languages[i], CultureInfo.GetCultureInfo(languages[i]).EnglishName);
            }
        } else {
            addColIndex = new KeyValuePair<string, string>[columnIndex.Count];
            foreach(KeyValuePair<string, int> lang in columnIndex) {
                addColIndex[lang.Value - editButtonColumNo - 1] = new KeyValuePair<string, string>(lang.Key, CultureInfo.GetCultureInfo(lang.Key).EnglishName);
            }
        }
        Session["addColIndex"] = addColIndex;
        KeyValuePair<string, string> currentLanguage;
        for(int i = 0; i < addColIndex.Length; i++) {
            currentLanguage = addColIndex[i];
            TableRow row = new TableRow();
            tblAddTranslation.Rows.Add(row);
            Label lbl = new Label();
            lbl.ID = "lblDyn" + currentLanguage.Key;
            lbl.Text = currentLanguage.Value;
            lbl.AssociatedControlID = "txtDyn" + currentLanguage.Key;
            TableCell celLanguage = new TableCell();
            celLanguage.CssClass = "language";
            row.Cells.Add(celLanguage);
            celLanguage.Controls.Add(lbl);
            TextBox txt = new TextBox();
            txt.ID = "txtDyn" + currentLanguage.Key;
            txt.TextMode = TextBoxMode.MultiLine;
            txt.Rows = 4;
            txt.EnableViewState = false;
            txt.ValidationGroup = "addelement";
            TableCell celTranslation = new TableCell();
            row.Cells.Add(celTranslation);
            celTranslation.Controls.Add(txt);
            if(currentLanguage.Key == defaultLanguage) {
                RequiredFieldValidator val = new RequiredFieldValidator();
                val.ControlToValidate = txt.ID;
                val.ErrorMessage = "The default language must be entered";
                val.SetFocusOnError = true;
                val.Display = ValidatorDisplay.Dynamic;
                val.ValidationGroup = "addelement";
                celLanguage.Controls.Add(val);
            }
            HyperLink btnFindTranslation = new HyperLink();
            btnFindTranslation.Text = "Find Similar Translations";
            btnFindTranslation.NavigateUrl = "#" + currentLanguage.Key; // language code here for consistency with grid, not actually used on the add form
            btnFindTranslation.CssClass = "findTranslation";
            btnFindTranslation.ToolTip = "Lookup translations for other languages using " + currentLanguage.Value;
            btnFindTranslation.ID = "dynfind-" + currentLanguage.Key;
            TableCell celActions = new TableCell();
            row.Cells.Add(celActions);
            celActions.Controls.Add(btnFindTranslation);

            jqueryBuffer.Append("$(\"#").Append(btnFindTranslation.ClientID).Append("\").click(function () {")
                            .Append("$(\"#").Append(celActions.ClientID).Append("\").find(\"#ajax-").Append(celActions.ClientID).Append("\").remove();\n")
                            .Append("$(\"<img/>\").attr({src:\"images/ajax-small.gif\",alt:\"Searching...\",id:\"ajax-").Append(celActions.ClientID).Append("\"}).addClass(\"ajax-small\").appendTo(\"#").Append(celActions.ClientID).Append("\");\n")
                            .Append("$.ajax({type:\"POST\",url:\"TranslationFinder.asmx/FindTranslation\",contentType:\"application/json; charset=utf-8\",")
                                    .Append("data:getTranslationData('").Append(txt.ClientID).Append("','").Append(currentLanguage.Key).Append("'),success: function(response) { \n")
                                        .Append("showMatchingTranslations(response, '").Append(celActions.ClientID).Append("', true);\n")
                                    .Append("}\n")
                            .Append("});return false;\n")
                            .Append("});\n");
        }
        if(!Page.ClientScript.IsStartupScriptRegistered("addtranslationfinder")) {
            Page.ClientScript.RegisterStartupScript(typeof(Page), "addtranslationfinder", jqueryBuffer.ToString(), true);
        }
    }

    /// <summary>
    /// Processes the selected bundles and languages and shows a grid to edit the translations.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnEditTranslations_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 2;
        InitializeDataSource();
        PopulateAddFormLanguages();
        cboAddBundleName.DataBind();
        InitializeGridView();
        grdTranslations.DataBind();
    }


    /// <summary>
    /// Adds a new element from the form.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnAddElement_Click(object sender, EventArgs e) {
        lblAddError.Visible = false;
        string bundleId = cboAddBundleName.SelectedValue,
               elementName = txtAddElementName.Text,
               elementComment = txtAddElementComment.Text,
               translationID = cboAddTranslationId.SelectedValue,
               translationComment = txtAddTranslationComment.Text;
        if(string.IsNullOrEmpty(elementName)) {
            lblAddError.Text = "Element name cannot be empty";
            lblAddError.Visible = true;
        }
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            if(translationID == "-1") {
                cmd.CommandText = "SELECT MAX(translationid) FROM translations";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                translationID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans) ON DUPLICATE KEY UPDATE translation = @trans";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE translations SET translation = @trans WHERE translationid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans)";
                    break;
            }

            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            foreach(string key in Request.Form.AllKeys) {
                if(key.Contains("txtDyn") && Request.Form[key].Length > 0) {
                    cmd.Parameters["@trans"].Value = Request.Form[key];
                    cmd.Parameters["@lang"].Value = key.Substring(key.LastIndexOf("txtDyn") + 6); // the ID of the textbox has $country-CODE appended to it
                    cmd.ExecuteNonQuery();
                }
            }
            if(translationComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE translationcomments SET comment = @comment WHERE translationid = @tID; IF @@ROWCOUNT = 0 INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", translationID);
                cmd.AddParameterWithValue("@comment", translationComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "INSERT INTO elements (applicationid, bundle, elementname, translationid, comment) VALUES (@pID, @bundle, @element, @tID, @comment)";
            cmd.AddParameterWithValue("@pID", dsMain.SelectParameters["pID"].DefaultValue);
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@bundle", bundleId);
            cmd.AddParameterWithValue("@element", elementName);
            cmd.AddParameterWithValue("@comment", elementComment);
            try {
                cmd.ExecuteNonQuery();
                InitializeDataSource();
                InitializeGridView();
                grdTranslations.DataBind();

                cboAddBundleName.SelectedIndex = 0;
                txtAddElementName.Text = string.Empty;
                txtAddElementComment.Text = string.Empty;
                txtAddTranslationComment.Text = string.Empty;
                PopulateAddFormLanguages();
            } catch {
                lblAddError.Text = "Element name already exists";
                lblAddError.Visible = true;
                txtAddElementName.Focus();
            }
        }
    }

    /// <summary>
    /// Handles the PageIndexChanging event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grd_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdTranslations.PageIndex = e.NewPageIndex;
        grdTranslations.DataBind();
    }

    /// <summary>
    /// Handles the Sorting event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grd_Sorting(object sender, GridViewSortEventArgs e) {
  /*      InitializeDataSource();
        InitializeGridView();
        //grdTranslations.DataBind();
        string oldExpression = grdTranslations.SortExpression,
               newExpression = e.SortExpression;
        if(oldExpression.IndexOf(newExpression) < 0) {
            if(oldExpression.Length > 0) {
                e.SortExpression = newExpression + "," + oldExpression;
            } else {
                e.SortExpression = newExpression;
            }
        } else {
            e.SortExpression = oldExpression;
        }*/
//        grdTranslations.DataBind();
    }

    /// <summary>
    /// Sets up the column generator for the translation grid using the selected languages.
    /// </summary>
    private void InitializeGridView() {
        List<string> tmpLanguages = new List<string>();
        foreach(ListItem itm in lstLanguages.Items) {
            if(itm.Selected) {
                tmpLanguages.Add(itm.Value);
            }
        }
        Dictionary<string, int> columnIndex = new Dictionary<string, int>();
        string defaultLanguage = Session["defaultLanguage"].ToString();
        tmpLanguages.Sort();
        tmpLanguages.Remove(defaultLanguage);
        tmpLanguages.Insert(0, defaultLanguage);
        string[] languages = tmpLanguages.ToArray();
        for(int i = 0; i < languages.Length; i++) {
            columnIndex[languages[i]] = i + editButtonColumNo + 1;
        }
        Session["colIndexes"] = columnIndex;
        grdTranslations.ColumnsGenerator = new LanguageColumnGenerator(languages, Session["defaultLanguage"].ToString());
    }

    /// <summary>
    /// Initializes the TranslationID and main DataSources taking the selected languages and bundles into account.
    /// </summary>
    private void InitializeDataSource() {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            string applicationId = radApplication.SelectedValue,
                   displayName = string.Empty,
                   defaultLanguage = string.Empty;
            cmd.CommandText = "SELECT displayname, defaultlanguage FROM applications WHERE id = @pID";
            cmd.AddParameterWithValue("@pID", applicationId);
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
            if(reader.HasRows) {
                reader.Read();
            }
            displayName = reader["displayname"].ToString();
            defaultLanguage = reader["defaultlanguage"].ToString();
            reader.Close();
            lblEditingTranslationsFor.Text = string.Format("Editing Translations for {0}", displayName);
            lblBreadcrumbEnd.Text = lblEditingTranslationsFor.Text;

            if(string.IsNullOrEmpty(dsMain.SelectCommand) || translationIds.Count < 5) {
                StringBuilder sqlBuffer = new StringBuilder();
                foreach(ListItem itm in lstBundles.Items) {
                    if(itm.Selected) {
                        sqlBuffer.Append("'" + itm.Value + "', ");
                    }
                }
                sqlBuffer.Remove(sqlBuffer.Length - 2, 1); // remove last comma
                string bundleSql = sqlBuffer.ToString();
                sqlBuffer.Remove(0, sqlBuffer.Length);
                string crossTabFormat = string.Empty;
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        crossTabFormat = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                        cmd.CommandText = @"SELECT DISTINCT translationid, 
                                            CAST(IF(CHAR_LENGTH(translation) < 15, 
                                                CONCAT(translationid, ' - ',translation), 
                                                CONCAT(translationid, ' - ',SUBSTRING(translation,1,12),'...')
                                                ) AS CHAR) 
                                            AS display FROM translations WHERE language = @defaultLang ORDER BY translationid ASC";
                        break;
                    case Database.DatabaseType.SQLServer:
                        crossTabFormat = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                        cmd.CommandText = @"SELECT DISTINCT translationid, display = CAST(translationid AS VARCHAR)+' - '+
                                            CASE
                                             WHEN LEN(translation) < 15 THEN translation
                                             ELSE SUBSTRING(translation,1,12)+'...'
                                            END
                                            FROM translations WHERE language = @defaultLang ORDER BY translationid ASC";
                        break;
                }
                foreach(ListItem itm in lstLanguages.Items) {
                    if(itm.Selected) {
                        sqlBuffer.AppendFormat(crossTabFormat, itm.Value);
                    }
                }
                sqlBuffer.Remove(sqlBuffer.Length - 1, 1); // remove last comma
                string crossTabColumns = sqlBuffer.ToString();
                Session["crossTabColumns"] = crossTabColumns;
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        dsMain.SelectCommand = @"SELECT elements.id, applicationid, bundle, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment, bundles.name AS bundlename," + crossTabColumns +
                                               @" FROM elements 
                                          INNER JOIN translations ON translations.translationid = elements.translationid 
                                          LEFT JOIN bundles ON bundles.id = elements.bundle
                                          LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid 
                                          WHERE applicationid = ?pID AND bundle IN (" + bundleSql + @") 
                                          GROUP BY elements.translationid 
                                          ORDER BY bundle, elementname ";
                        
                        break;
                    case Database.DatabaseType.SQLServer:
                        dsMain.SelectCommand = @"SELECT elements.id, applicationid, bundle, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment, bundles.name AS bundlename, " + crossTabColumns +
                                               @"FROM elements
                                            INNER JOIN translations ON translations.translationid = elements.translationid
                                            LEFT JOIN bundles ON bundles.id = elements.bundle
                                            LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid
                                        WHERE applicationid = @pID  AND bundle IN (" + bundleSql + @") 
                                        GROUP BY elements.translationid,
                                                 elements.id,
                                                 applicationid,
                                                 bundle,
                                                 elementname,
                                                 elements.comment,
                                                 translationcomments.comment,
                                                 bundles.name
                                        ORDER BY bundle, elementname";
                        
                        break;
                }
                dsBundles.SelectCommand = "SELECT id, name FROM bundles WHERE id IN (" + bundleSql + ")";
                grdTranslations.DataSource = dsMain;

                if(dsMain.SelectParameters.Count == 0) {
                    dsMain.SelectParameters.Add("pID", TypeCode.String, applicationId);
                }
                cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
                reader = cmd.ExecuteReader();
                translationIds.Clear();
                translationIds.Add(-1, "New Translation");
                while(reader.Read()) {
                    translationIds.Add(int.Parse(reader["translationid"].ToString()), reader["display"].ToString());
                }
                reader.Close();
            }
            cboAddBundleName.DataBind();
            cboAddTranslationId.DataSource = translationIds;
            cboAddTranslationId.DataTextField = "Value";
            cboAddTranslationId.DataValueField = "Key";
            cboAddTranslationId.DataBind();
            Session["defaultLanguage"] = defaultLanguage;
        }
    }

    /// <summary>
    /// Databinds the BundleName and TranslationID comboboxes and assigns JavaScript listeners.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewRowEventArgs"/> instance containing the event data.</param>
    protected void grd_RowDataBound(object sender, GridViewRowEventArgs e) {
        if(e.Row.RowType == DataControlRowType.DataRow) {
            DataRowView view = e.Row.DataItem as DataRowView;
            DropDownList cboBundleName = e.Row.FindControl("cboBundleName") as DropDownList;
            cboBundleName.DataBind();
            cboBundleName.SelectedValue = view["bundle"].ToString();// (e.Row.FindControl("lblBundleName") as Label).Text;

            DropDownList cboTranslationId = e.Row.FindControl("cboTranslationID") as DropDownList;
            cboTranslationId.DataSource = translationIds;
            cboTranslationId.DataTextField = "Value";
            cboTranslationId.DataValueField = "Key";
            cboTranslationId.DataBind();
            cboTranslationId.SelectedValue = view["translationid"].ToString();

            // find the JS callback which is assigned to the 'Change Translation' button
            Button btnChangeTranslation = e.Row.FindControl("btnChangeTranslation") as Button;
            string jsChangeTranslation = ClientScript.GetPostBackClientHyperlink(btnChangeTranslation, "");
            cboTranslationId.Attributes["onchange"] = jsChangeTranslation; // assign the callback to the dropdown list so a button press is simulated when the index changes

            TextBox txtElementComment = (e.Row.FindControl("txtElementComment") as TextBox);
            if(!String.IsNullOrEmpty(txtElementComment.Text)) {
                e.Row.Cells[2].ToolTip = txtElementComment.Text;
                e.Row.Cells[2].CssClass = "ssCell commentCell";
            }   
            TextBox txtTranslationComment = (e.Row.FindControl("txtTranslationComment") as TextBox);
            if(!String.IsNullOrEmpty(txtTranslationComment.Text)) {
                e.Row.Cells[3].ToolTip = txtTranslationComment.Text;
                e.Row.Cells[3].CssClass = "ssCell commentCell";
            }
        }
    }

    protected void dsMain_Selected(object sender, SqlDataSourceStatusEventArgs e) {
        Session["translations"] = e.AffectedRows;
    }

    protected void grd_PreRender(object sender, EventArgs e) {
        base.OnPreRender(e);
        GridViewRow pagerRow = grdTranslations.TopPagerRow;
        if(pagerRow != null) {
            TableCell cell = pagerRow.Cells[0];
            (cell.Controls[0] as Table).CssClass = "inline";
            Literal lblNumPages = new Literal();
            cell.Controls.Add(lblNumPages);
            lblNumPages.Text = string.Format("<div class=\"itemCount\">Showing {0}-{1} of {2}</div>", grdTranslations.Rows[0].DataItemIndex + 1, grdTranslations.Rows[grdTranslations.Rows.Count - 1].DataItemIndex + 1, Session["translations"]);
        }
        StringBuilder jqueryBuffer = new StringBuilder("var prm = Sys.WebForms.PageRequestManager.getInstance(); prm.add_endRequest(function() { \n");
        if(grdTranslations.SelectedIndex >= 0 && grdTranslations.Rows.Count > grdTranslations.SelectedIndex) {
            GridViewRow row = grdTranslations.Rows[grdTranslations.SelectedIndex];
            // The control indexes of the textbox and image buttons to which to attach event listeners
            View view;
            TextBox txt;
            HyperLink btn;
            string rowId;
            for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                rowId = row.Cells[i].ClientID;
                view = (row.Cells[i].Controls[0] as MultiView).Views[1];
                txt = view.Controls[0] as TextBox;
                btn = view.Controls[2] as HyperLink;
                jqueryBuffer.Append("$(\"#").Append(btn.ClientID).Append("\").click(function () {\n")
                            .Append("$(\"#").Append(rowId).Append("\").find(\"#ajax-").Append(rowId).Append("\").remove();\n")
                            .Append("$(\"<img/>\").attr({src:\"images/ajax-small.gif\",alt:\"Searching...\",id:\"ajax-").Append(rowId).Append("\"}).addClass(\"ajax-small\").appendTo(\"#").Append(rowId).Append("\");\n")
                            .Append("$.ajax({type:\"POST\",url:\"TranslationFinder.asmx/FindTranslation\",contentType:\"application/json; charset=utf-8\",")
                                    .Append("data:getTranslationData('").Append(txt.ClientID).Append("','").Append(btn.NavigateUrl.Substring(1)).Append("'),success: function(response) { ")
                                        .Append("showMatchingTranslations(response, '").Append(rowId).Append("', false);")
                                    .Append("}\n")
                            .Append("});return false;\n")
                            .Append("});\n");
            }
        }
        // the add form is also in the update panel, so re-register the click events
        for(int i=0;i<tblAddTranslation.Rows.Count;i++) {
            TableRow addRow = tblAddTranslation.Rows[i];
            TableCell celLanguage = addRow.Cells[0];
            Label lbl = celLanguage.Controls[0] as Label;
            string abbrev = lbl.ID.Replace("lblDyn", "");
            TableCell celTranslation = addRow.Cells[1];
            TextBox addTxt = celTranslation.Controls[0] as TextBox;
            TableCell celActions = addRow.Cells[2];
            HyperLink btnFindTranslation = celActions.Controls[0] as HyperLink;
            jqueryBuffer.Append("$(\"#").Append(btnFindTranslation.ClientID).Append("\").click(function () {\n")
                            .Append("$(\"#").Append(celActions.ClientID).Append("\").find(\"#ajax-").Append(celActions.ClientID).Append("\").remove();\n")
                            .Append("$(\"<img/>\").attr({src:\"images/ajax-small.gif\",alt:\"Searching...\",id:\"ajax-").Append(celActions.ClientID).Append("\"}).addClass(\"ajax-small\").appendTo(\"#").Append(celActions.ClientID).Append("\");\n")
                            .Append("$.ajax({type:\"POST\",url:\"TranslationFinder.asmx/FindTranslation\",contentType:\"application/json; charset=utf-8\",")
                                    .Append("data:getTranslationData('").Append(addTxt.ClientID).Append("','").Append(abbrev).Append("'),success: function(response) { ")
                                        .Append("showMatchingTranslations(response, '").Append(celActions.ClientID).Append("', true);")
                                    .Append("}\n")
                            .Append("});return false;\n")
                            .Append("});\n");
            }
        jqueryBuffer.Append("});");
        //System.Diagnostics.Debug.WriteLine(jqueryBuffer.ToString());
        ScriptManager.RegisterStartupScript(this, typeof(Page), "translationfinder", jqueryBuffer.ToString(), true);
    }

    /// <summary>
    /// Handles RowCommand events from the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grd_RowCommand(object sender, GridViewCommandEventArgs e) {
        InitializeDataSource();
        InitializeGridView();
        grdTranslations.DataBind();
        GridViewRow row;  // The row which has clicked was the undatabound one, so use it's index to lookup the new, databound row
        switch(e.CommandName) {
            case "edittranslation": // 'Edit' button clicked
                row = grdTranslations.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ToggleEditControls(row, true);
                grdTranslations.SelectedIndex = row.RowIndex;
                break;
            case "canceledit":
                row = grdTranslations.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                ToggleEditControls(row, false);
                grdTranslations.SelectedIndex = -1;
                break;
            case "changetranslation": // index of dropdownlist changed
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                string selectedTranslationID = (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList).SelectedValue;
                ChangeDisplayedTranslation(row, selectedTranslationID);
                break;
            case "savetranslation": // 'Save' button clicked
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                UpdateTranslation(row, e.CommandArgument.ToString());
                grdTranslations.SelectedIndex = -1;
                break;
            case "deleteelement":
                row = grdTranslations.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ToggleEditControls(row, false);
                (row.FindControl("mltActions") as MultiView).ActiveViewIndex = 2;
                break;
            case "confirmdelete":
                DeleteElement(e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Deletes the specified element.
    /// </summary>
    /// <param name="elementID">The element ID.</param>
    private void DeleteElement(string elementID) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "DELETE FROM elements WHERE applicationid = @pID AND id = @eID";
            cmd.AddParameterWithValue("@pID", dsMain.SelectParameters["pID"].DefaultValue);
            cmd.AddParameterWithValue("@eID", elementID);
            cmd.ExecuteNonQuery();
        }
        grdTranslations.DataBind();
    }

    /// <summary>
    /// Shows the textbox controls to edit a translation.
    /// </summary>
    /// <param name="row">The row for which to show the controls.</param>
    /// <param name="visible"><c>true</c> to show the controls, <c>false to hide them</c>.</param>
    private void ToggleEditControls(GridViewRow row, bool visible) {
        int oldRowActiveIndex = visible ? 0 : 1,
            newRowActiveIndex = visible ? 1 : 0;
        if(grdTranslations.SelectedIndex >= 0 && grdTranslations.Rows.Count > grdTranslations.SelectedIndex) {
            GridViewRow oldRow = grdTranslations.SelectedRow;
            for(int i = editButtonColumNo+1; i < row.Cells.Count; i++) {
                (oldRow.Cells[i].Controls[0] as MultiView).ActiveViewIndex = oldRowActiveIndex;
            }
        }

        (row.FindControl("mltBundle") as MultiView).ActiveViewIndex = newRowActiveIndex;
        (row.FindControl("mltElement") as MultiView).ActiveViewIndex = newRowActiveIndex;
        (row.FindControl("mltActions") as MultiView).ActiveViewIndex = newRowActiveIndex;
        row.Cells[2].CssClass = "ssCell"; // remove the comment indicators
        row.Cells[3].CssClass = "ssCell";
        for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
            (row.Cells[i].Controls[0] as MultiView).ActiveViewIndex = newRowActiveIndex;
        }
    }

    /// <summary>
    /// Changes the translation selected for an element.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="selectedTranslation">The new translation ID.</param>
    private void ChangeDisplayedTranslation(GridViewRow oldRow, string selectedTranslation) {
        GridViewRow row = grdTranslations.Rows[oldRow.RowIndex]; // the index of the old row in the new grid
        ToggleEditControls(row, true);
        (row.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue = (oldRow.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue;
        (row.Cells[2].FindControl("txtElementName") as TextBox).Text = (oldRow.Cells[2].FindControl("txtElementName") as TextBox).Text;
        (row.Cells[2].FindControl("txtElementComment") as TextBox).Text = (oldRow.Cells[2].FindControl("txtElementComment") as TextBox).Text;
        DropDownList cboTranslationID = (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList);
        cboTranslationID.SelectedValue = selectedTranslation;
        row.CssClass = "ssRowSelected";
        int textBoxControlIndex = 0;
        if(selectedTranslation == "-1") {
            for(int i = editButtonColumNo+1; i < row.Cells.Count; i++) {
                ((row.Cells[i].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = "";
            }
        } else {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT " + Session["crossTabColumns"] + " FROM translations WHERE translationid = @tID";
                cmd.AddParameterWithValue("@tID", selectedTranslation);
                DbDataReader reader = cmd.ExecuteReader();
                Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
                if(reader.HasRows) {
                    reader.Read();
                    object translation;
                    for(int i = 0; i < reader.FieldCount; i++) {
                        translation = reader[i];
                       ((row.Cells[columnIndex[reader.GetName(i).ToString()]].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = translation == null ? "" : translation.ToString();
                    }
                }
                reader.Close();

                cmd.CommandText = "SELECT comment FROM translationcomments WHERE translationid = @tID";
                reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    (row.Cells[editButtonColumNo].FindControl("txtTranslationComment") as TextBox).Text = reader.GetString(0);
                } else {
                    (row.Cells[editButtonColumNo].FindControl("txtTranslationComment") as TextBox).Text = string.Empty;
                }
            }
        }
    }

    /// <summary>
    /// Changes the translation displayed on the 'New Element' form.
    /// </summary>
    /// <param name="sender">The event source.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void ChangeAddDisplayedTranslation(object sender, EventArgs e) {
        int selectedTranslation = int.Parse(cboAddTranslationId.SelectedValue);
        InitializeDataSource();
        InitializeGridView();
        grdTranslations.DataBind();
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT " + Session["crossTabColumns"] + " FROM translations WHERE translationid = @tID";
            cmd.AddParameterWithValue("@tID", selectedTranslation.ToString());
            Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
            DbDataReader reader = cmd.ExecuteReader();
            if(reader.HasRows) {
                reader.Read();
                object translation;
                for(int i = 0; i < reader.FieldCount; i++) {
                    translation = reader[i];
                    (tblAddTranslation.Rows[columnIndex[reader.GetName(i).ToString()]-editButtonColumNo-1].Cells[1].Controls[0] as TextBox).Text = translation == null ? "" : translation.ToString();
                }
            }
            reader.Close();

            cmd.CommandText = "SELECT comment FROM translationcomments WHERE translationid = @tID";
            reader = cmd.ExecuteReader();
            if(reader.HasRows) {
                reader.Read();
                txtAddTranslationComment.Text = reader.GetString(0);
            } else {
                txtAddElementComment.Text = string.Empty;
            }
        }
        cboAddTranslationId.SelectedValue = selectedTranslation.ToString();
    }

    /// <summary>
    /// Updates a translation.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="id">The id.</param>
    private void UpdateTranslation(GridViewRow oldRow, string id) {
        GridViewRow newRow = grdTranslations.Rows[oldRow.RowIndex];
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            Label lblError = newRow.Cells[editButtonColumNo].FindControl("lblError") as Label;
            string bundleId = (oldRow.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue,
                   elementName = (oldRow.Cells[2].FindControl("txtElementName") as TextBox).Text,
                   elementComment = (oldRow.Cells[2].FindControl("txtElementComment") as TextBox).Text,
                   translationID = (oldRow.Cells[3].FindControl("cboTranslationID") as DropDownList).SelectedValue,
                   translationComment = (oldRow.Cells[3].FindControl("txtTranslationComment") as TextBox).Text;

            if(translationID == "-1") {
                cmd.CommandText = "SELECT MAX(translationid) FROM translations";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                translationID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans) ON DUPLICATE KEY UPDATE translation = @trans";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE translations SET translation = @trans WHERE translationid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans)";
                    break;
            }
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            string textBoxID;
            for(int i = editButtonColumNo + 1; i < newRow.Cells.Count; i++) {
                textBoxID = (newRow.Cells[i].Controls[0] as MultiView).Views[1].Controls[0].UniqueID;
                foreach(string key in Request.Form.AllKeys) {
                    if(key == textBoxID && Request.Form[key].Length > 0) {
                        cmd.Parameters["@trans"].Value = Request.Form[key];
                        cmd.Parameters["@lang"].Value = textBoxID.Substring(textBoxID.LastIndexOf("$") + 1); // the ID of the textbox has $country-CODE appended to it
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            if(translationComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE translationcomments SET comment = @comment WHERE translationid = @tID; IF @@ROWCOUNT = 0 INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", translationID);
                cmd.AddParameterWithValue("@comment", translationComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE elements SET bundle = @bundle, elementname = @element, translationid = @tID, comment = @comment WHERE id = @id";
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@bundle", bundleId);
            cmd.AddParameterWithValue("@element", elementName);
            cmd.AddParameterWithValue("@comment", elementComment);
            cmd.AddParameterWithValue("@id", id);
            try {
                cmd.ExecuteNonQuery();
                cboAddTranslationId.DataBind();
                grdTranslations.DataBind();
            } catch {
                ToggleEditControls(newRow, true);
                newRow.CssClass = "ssRowSelected";
                lblError.Text = "Element name already exists";
                lblError.Visible = true;
            }
        }
    }

    /// <summary>
    /// Switches back to the first form, retaining the selected values.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkNavEditOnline_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 1;
    }

    /// <summary>
    /// Expands/contracts the grid to fill the whole window
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkExpandScreen_Click(object sender, EventArgs e) {
        if(fullsizeholder.Attributes["class"] == "ssFullSize") {
            lnkExpandScreen.Text = "Expand to fill window";
            lnkExpandScreen.CssClass = "lnkExpand";
            fullsizeholder.Attributes["class"] = "ssContracted";
        } else {
            lnkExpandScreen.Text = "Contract to normal size";
            lnkExpandScreen.CssClass = "lnkContract";
            fullsizeholder.Attributes["class"] = "ssFullSize";
        }
    }
    
    
}
